package com.peace.hospital.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.peace.hospital.entity.common.Department;

import com.peace.hospital.entity.common.Staffinfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;


/**
 * <p>
 * 医院员工信息表 Mapper 接口
 * </p>
 *
 * @author xie
 * @since 2019-11-12
 */
@Mapper
public interface StaffinfoMapper extends BaseMapper<Staffinfo> {


    /**
     * 首页信息
     *
     * @return
     */
    @Select("select stajobnumber,staid,depid,stasex,staage,stabir,staaddress,statel,staname,stanstate FROM hospital_public.staffinfo where stanstate<>'离职'")


    @Results({
            @Result(column = "staid", property = "staid"),
            @Result(column = "stajobnumber", property = "stajobnumber"),
            @Result(column = "depid", property = "depid"),
            @Result(column = "stasex", property = "stasex"),
            @Result(column = "staage", property = "staage"),
            @Result(column = "stabir", property = "stabir"),
            @Result(column = "staaddress", property = "staaddress"),
            @Result(column = "statel", property = "statel"),
            @Result(column = "staname", property = "staname"),
            @Result(column = "stanstate", property = "stanstate"),
            @Result(column = "depid", property = "department", one = @One(select = "com.peace.hospital.mapper.DepartmentMapper.selectDepartment", fetchType = FetchType.LAZY)),
            @Result(column = "staid", property = "staffinfoandpositionList", many = @Many(select = "com.peace.hospital.mapper.StaffinfoandpositionMapper.SelectPositionId", fetchType = FetchType.LAZY)),
    })
    List<Staffinfo> SelcctStaffinfoMessage();


    /**
     * 根据科室名查询职位ID
     *
     * @param deptype
     * @return
     */
    @Select("select depid  from hospital_public.department  where deptype=#{deptype}")
    Department SelcctDepid(String deptype);


    /**
     * 修改用户信息
     *
     * @param staffinfos
     * @return
     */
    @Update("update hospital_public.staffinfo set staname=#{staname},stasex=#{stasex},staage=#{staage},staaddress=#{staaddress}, statel=#{statel} ,depid=#{depid} where staid=#{staid}")
    Integer UpdateStaffinfo(Staffinfo staffinfos);


    /**
     * 修改之前把员工与职位信息删除掉
     *
     * @param
     * @return
     */
    @Delete(" delete from  hospital_public.staffinfoandposition  where  staid=#{staids}")
    Integer deletestaffinfoandposition(Integer staids);


    //修改中间表对应关系
    @Insert("INSERT INTO hospital_public.staffinfoandposition (posid, staid) VALUES(#{pos},#{sta})")
    Integer InsertMassge(@Param("pos") Integer pos, @Param("sta") Integer sta);


    //修改员工状态
    @Update("update hospital_public.staffinfo set stanstate='离职' where staid=#{staid}")
    Integer UpdateStanstate(Integer staid);


    //添加之前先查询员工是否存在
//    @Select("select staid FROM hospital_public.staffinfo where empId=#{statel} or name like #{statel}")
    @Select("select staid FROM hospital_public.staffinfo where statel=#{statel}")
    Staffinfo slectByStatel(String statel);

    //添加员工
    @Insert("INSERT INTO hospital_public.staffinfo(staname,stasex,staage,stabir,staaddress,statel,depid,stadbsource,stanstate,stapassword,stajobnumber)VALUES(#{staname},#{stasex},#{staage},#{stabir},#{staaddress},#{statel},#{depid},#{stadbsource},#{stanstate},#{stapassword},#{stajobnumber})")
    int InsertStaffinfo(Staffinfo staffinfo);


    //多条件查询用户信息
    @Select("select stajobnumber,staid,depid,stasex,staage,stabir,staaddress,statel,staname,stanstate FROM hospital_public.staffinfo where  statel=#{statel} or staname  like CONCAT('%',(#{staname}),'%')")
    @Results({                                                                                                                                                  //or staname  like  '%${staname}%'
            @Result(column = "staid", property = "staid"),
            @Result(column = "stajobnumber", property = "stajobnumber"),
            @Result(column = "depid", property = "depid"),
            @Result(column = "stasex", property = "stasex"),
            @Result(column = "staage", property = "staage"),
            @Result(column = "stabir", property = "stabir"),
            @Result(column = "staaddress", property = "staaddress"),
            @Result(column = "statel", property = "statel"),
            @Result(column = "staname", property = "staname"),
            @Result(column = "stanstate", property = "stanstate"),
            @Result(column = "depid", property = "department", one = @One(select = "com.peace.hospital.mapper.DepartmentMapper.selectDepartment", fetchType = FetchType.LAZY)),
            @Result(column = "staid", property = "staffinfoandpositionList", many = @Many(select = "com.peace.hospital.mapper.StaffinfoandpositionMapper.SelectPositionId", fetchType = FetchType.LAZY)),
    })
    //CONCAT('%',(#{staname}),'%')
    List<Staffinfo>  SelectLike(Staffinfo staffinfo);

   //查询员工最大ID
    @Select("select count(staid) from hospital_public.staffinfo")
     Integer SelectStaid();
















//    /**
//     * 首页信息
//     * @return
//     */
//    @Select(
//        "SELECT stajobnumber,staid,sta.depid,stasex,staage,stabir,staaddress,statel,staname,deptype  " +
//                "FROM hospital_public.staffinfo AS sta INNER  JOIN hospitalization.department AS dep ON sta.depid=dep.depid"
//)
//@Results({
//        @Result(column ="deptype",property = "department.deptype"),
//        @Result( column = "depid", property = "department.depid")
//
//})
//    @Select({"<script> " +
//            "select stajobnumber,staid,depid,stasex,staage,stabir,staaddress,statel,staname  hospital_public.staffinfo" +
//            "where staid=#{staid} " +
//            "<if test='staid!=null'> and staid = #{userId}</if> " +
//            "</script>"})

    //    /**
////     * 在中间表修改用户对应的职位
////     * @param posid
////     * @param staid
////     * @return
////     */
////    @Update("update hospital_public.staffinfoandposition set posid=#{posid} where staid=#{staids}")
////
////    Integer   UpdateStaffinfoandposition(@Param("posid") Integer posid,@Param("staids") Integer staid );

    //    /**
//     * 根据职位名查询职位ID
//     * @param postitle
//     * @return
//     */
//    @Select("select posid from system.position where postitle=#{postitle}")
//    Position SelcctPosid (String postitle);

//
//    @Insert({
//            "<script>",
//            "insert into  hospital_public.staffinfoandposition(posid,staid) values ",
//            "<foreach collection='postitleList' item='item' index='index' separator=','>",
//            "(#{item.postitleList},#{sta})",
//            "</foreach>",
//            "</script>"
//    })
//    int insertCollectList(@Param("postitleList") Integer postitle ,@Param("sta") Integer sta);

//    collection： 指定要遍历的集合（三种情况 list，array，map） ！！！！在这种使用注解sql的情况下，这里请填写mapper方法中集合的名称
//
//    item：将当前遍历出的元素赋值给指定的变量 （相当于for循环中的i）
//    separator:每个元素之间的分隔符
//    index:索引。遍历list的时候是index就是索引，item就是当前值


//    /**
//     * 修改员工信息
//     * @param staffinfo
//     * @return
//     */
//    //@param 的作用是给参数起个别名当有多个参数时用来区分
//    //通过映射关系直接获取对象中的属性值
//    @Update("update hospital_public.staffinfo set staname =#{staffinfo},stasex=#{staffinfo},staage=#{staffinfo},staaddress=#{staffinfo}, statel=#{staffinfo} where staid=#{staffinfo} ")
//
//           Integer   UpdateStaffinfo(@Param("staffinfo") Staffinfo staffinfo);

}
